═══ 1. Introduction ═══ Copyright 1993 Ammonoosuc Technology, Inc. SQLEdit allows the user to enter an arbitrary SQL SELECT query, run an existing stored query, edit and run an existing stored query, or execute a query stored in a file. The output of this query is displayed in a window on the screen. The user may then choose to INSERT a new row using an existing output row as a starting point and editing it to create the new row. (S)he may also choose to DELETE in the base table a row which is being displayed. As a third choice, (s)he may UPDATE selected columns in an existing output row. When editing of the data being displayed has been completed, the user then tells the program to actually perform the data base modifications. Each modified or new row becomes the subject of a separate SQL INSERT, UPDATE, or DELETE transaction. Some (or all) of the transactions may fail if they are invalid. Reasons for invalidity are manifold. They may include lack of authorization, modifying a table which is actually a read-only view, etc. In any case, for every failing transaction, a suitable SQL error message will be displayed. ═══ 2. Data Base Selection ═══ Upon activation, the program will display a list of existing data bases. The user must select one of them which will become the active database for the current session. Selection may be accomplished either by selecting an entry and then pressing the ENTER push button, or by double clicking on the desired entry. It is assumed that the user is familiar with the contents of each of the data bases and knows which (s)he wants to edit. No browsing facilities are included with this program. If there is only a single data base, this selection process will be bypassed. ═══ 3. Query Selection and Composition ═══ At this point, the user may select from a menu bar either to execute an ad hoc (COMPOSED) SQL query or to execute or edit an existing stored query. This query may be stored either in the database or in an external file. If (s)he chooses to compose an ad hoc query, (s)he will be presented with a window in which the query may be composed. When (s)he is done composing the query, (s)he should press the ENTER button and the query will be executed. If the user chooses to work with a stored query and there are no stored queries, the stored query section of the menu bar will be disabled and (s)he must choose to either compose a query or select a file containing a query. If the user chooses to work with a query stored in the database and there is more than one stored query in the database, a list of all the stored queries in the database will be presented. The user must choose one of them. Selection may be accomplished either by selecting an entry and then pressing the ENTER push button, or by double clicking on the desired entry. If there is only a single stored query in the data base, this selection is bypassed. If the user has elected to edit an existing stored query, the text of the stored query will be displayed in an edit window where it may be modified. When editing is completed, the user should press the ENTER button and the edited query will be executed. Otherwise, if editing is not invoked, an existing stored query will be directly executed. If the user specifies a query stored in an external file, it is assumed that this file is a text file containing a single query. If this option is selected, a file selection box will be presented to the user. When a file has been selected, it will be read and the contents executed as a query. The text in this file will be completely unedited when it is executed as a query. ═══ 4. Query Execution and Data Presentation ═══ When the SQL query is executed, if execution is unsuccessful, a standard SQL error message will be displayed explaining why the query is in error. A further explanation of the error may be found in the normal SQL error message manual. At this point, the query selection process will restart. Otherwise, the output of the query will be displayed in a window. The format of this output display depends only on the stored data and is independent of any stored forms or other external formatting information. The order in which columns are displayed depends only on their order in the query. All data types may be displayed in this window except LONG VARCHAR. Queries that return LONG VARCHAR data are not supported. This window may be scrolled both horizontally and vertically if necessary to display all the data. This window is fixed in size and may not be resized. ═══ 5. Creating INSERT Transactions ═══ The philosophy behind executing an INSERT transaction is to choose an existing row as a model for the new row, duplicate it, and edit it until we have the row we want to insert, keeping th number of columns the same. To accomplish this , single click on the INSERT button using mouse button 1. Then position the cursor anywhere on the row to be duplicated and single click using mouse button 2. This will cause a copy of the selected row to be appended to the end of the display. This appended row will be displayed in the same color as the INSERT button. Note that this is independent of whether the underlying table permits duplicate rows or not. This duplicate row may now be edited by positioning the cursor on this row and single clicking the SELECT push button using mouse button 1. Now position the cursor on the data item in the row to be edited and press the RIGHT mouse button. This will cause the selected data item to be displayed in a separate window where it may be edited. This procedure may be repeated for as many data items within the row as required. Note that editing character data does not require that the data be enclosed in single quotes. The only time quotes should be used for character data is to include leading or trailing blanks. ═══ 6. Creating DELETE Transactions ═══ In order to prepare for a DELETE transaction, single click the DELETE push button using mouse button 1. Next position the cursor anywhere in the row to be deleted and single click with mouse button 2. This row is now marked for deletion and may not be further manipulated. It will be displayed in the same color as the DELETE push button. ═══ 7. Creating UPDATE Transactions ═══ To prepare for an UPDATE transaction, single click the UPDATE push button using mouse button 1. Next position the cursor on a specific data item to be updated and single click with mouse button 2. The selected data item will now be displayed in a separate window where it may be edited. The selected row will also be displayed in the same color as the UPDATE push button. This procedure may be repeated as often as desired on a single row in order to update the data in multiple columns. ═══ 8. Committing the edited data ═══ All the above activity simply prepares data for the appropriate SQL transaction. To actually execute the SQL transactions, click on the EXECUTE push button. At this point, a new window will appear and the user will be requested to enter the name of the table against which all the INSERT, UPDATE, and DELETE transactions will be executed. This table name is totally independent of the table name (or names) specified in the original SQL query. Note that this feature gives the user a limited copy capability by executing INSERT transactions against a different table. After the table name has been entered, separate transactions will be executed for each designated row. If these transactions are successful, each one is individually followed immediately by a COMMIT transaction. If the transaction is unsuccessful, an appropriate SQL error message will be displayed. Processing will continue with the next row after the error message has been acknowledged.. Once all the transactions have completed, the program returns to the main menu where the user is given the option of either terminating the program or executing a new SQL SELECT query. ═══ 9. Locking the data base ═══ The program accepts a single command line parameter. This parameter controls the isolation level of the database. This command line option consists of either a / or a - character. It is immediately followed by the letter L (case unimportant). This is immediately followed by the character 1, 2, or 3. 1 specifies Uncommitted Read isolation. 2 specifies Cursor Stability isolation and is the default. 3 specifies Repeatable Read isolation. If an invalid value of this parameter is specified, it is simply ignored and the default value is used. For a discussion of the significance of these terms, consult the OS/2 DBM documentation.